#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
__title__ = ''
__author__ = 'ben'
__mtime__ = '4/28/16'
"""

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

from mysqlFactory.database_url import CDataBaseUrl

Base = declarative_base()
db_handler = CDataBaseUrl('mysql')
MySql_Uri = {
    'host': '192.168.51.32',
    'port': 3306,
    'database': 'wydata',
    'user': 'root',
    'password': 'datagather'
}

db_url = db_handler.make_db_url(MySql_Uri)
engine = create_engine(db_url, encoding='utf-8', echo=True, pool_recycle=5)
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)

filter_cols = ['pk_id', 'id', 'user_register_info_id', 'del_flag', 'insert_datetime']

class CCompanyORM(object):

    def __init__(self):
        self.session = Session()
        self.tb_name_cls = {
            'wy_company_base_info':             CWyCmpBaseInfo,
            'wy_cmp_aptitude_info':             CWyCmpAptitudeInfo,
            'wy_cmp_branch_info':               CWyCmpBranchInfo,
            'wy_cmp_evolution_log_info':        CWyCmpEvolutionInfo,
            'wy_cmp_main_manager_info':         CWyCmpMainManagerInfo,
            'wy_cmp_ownership_structure_info':  CWyCmpOwnershipInfo,
            'wy_cmp_external_guarantee_info':   CWyCmpGuaranteeInfo,
            'wy_cmp_penalty_info':              CWyCmpPenaltyInfo
        }

    def get_data(self, col_value, tb_name='wy_company_base_info', display_null=True):
        """
        :param col_value: 要查询的公司的名称
        :param tb_name: 查询所在的表
        :param display_null: 是否显示空值
        """

        if not col_value:
            return

        if not self.tb_name_cls.has_key(tb_name):
            return

        result = []

        cls = self.tb_name_cls.get(tb_name)
        record_list = self.session.query(cls).filter(cls.cmp_name == col_value).all()

        for record in record_list:

            kv = record._sa_instance_state.dict
            if isinstance(kv, dict):
                if kv.has_key('_sa_instance_state'):
                    kv.pop('_sa_instance_state')

                for filter_k in filter_cols:
                    if kv.has_key(filter_k):
                        kv.pop(filter_k)

                if not display_null:
                    for k, v in kv.items():
                        if not v:
                            kv.pop(k)

            result.append(kv)

        self.session.close()

        return result

    def get_fuzzy_name(self, keyword, tb_name='wy_company_base_info'):
        """
        :param keyword:模糊匹配的关键字
        :param tb_name:查询的表
        """

        if not keyword:
            return

        if not self.tb_name_cls.has_key(tb_name):
            return

        result = []

        cls = self.tb_name_cls.get(tb_name)
        record_list = self.session.query(cls).filter(cls.cmp_name.like(('%%%s%%' % keyword)))[0:15]

        for record in record_list:
            cmp_name = record.cmp_name
            result.append(cmp_name)

        self.session.close()

        return result


    def get_all_info(self, col_value, display_null=True):
        """
        :param col_value:检索的公司名
        """

        if not col_value:
            return

        result = {}

        for tb_name in self.tb_name_cls.keys():
            cls = self.tb_name_cls.get(tb_name)
            record_list = self.session.query(cls).filter(cls.cmp_name == col_value)

            temp = []
            for record in record_list:
                kv = record._sa_instance_state.dict
                if isinstance(kv, dict):
                    if kv.has_key('_sa_instance_state'):
                        kv.pop('_sa_instance_state')

                    for filter_k in filter_cols:
                        if kv.has_key(filter_k):
                            kv.pop(filter_k)

                    if not display_null:
                        for k, v in kv.items():
                            if not v:
                                kv.pop(k)

                temp.append(kv)

            result[tb_name] = temp

        self.session.close()

        return result


class CWyCmpBaseInfo(Base):
    '''
    公司基本信息
    '''
    __tablename__ = 'wy_company_base_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    user_register_info_id = Column('user_register_info_id', String)
    cmp_english_name = Column('cmp_english_name', String)
    legaler = Column('legaler', String)
    company_nature = Column('company_nature', Integer)
    company_nature_text = Column('company_nature_text', String)
    operate_start_date = Column('operate_start_date', DateTime)
    operate_end_date = Column('operate_end_date', DateTime)
    establishment_date = Column('establishment_date', DateTime)
    national_fax_code = Column('national_fax_code', String)
    local_fax_code = Column('local_fax_code', String)
    registration_agency = Column('registration_agency', String)
    bank_name = Column('bank_name', String)
    bank_code = Column('bank_code', String)
    foreign_bank_name = Column('foreign_bank_name', String)
    foreign_bank_code = Column('foreign_bank_code', String)
    registered_capital = Column('registered_capital', Float)
    reg_province = Column('reg_province', String)
    reg_province_code = Column('reg_province_code', String)
    reg_city = Column('reg_city', String)
    reg_city_code = Column('reg_city_code', String)
    reg_zip = Column('reg_zip', String)
    reg_address = Column('reg_address', String)
    business_province = Column('business_province', String)
    business_province_code = Column('business_province_code', String)
    business_city = Column('business_city', String)
    business_city_code = Column('business_city_code', String)
    business_zip = Column('business_zip', String)
    business_address = Column('business_address', String)
    business_scope = Column('business_scope', String)
    contact = Column('contact', String)
    contact_phone = Column('contact_phone', String)
    fixed_number = Column('fixed_number', String)
    fax_number = Column('fax_number', String)
    e_mail = Column('e_mail', String)
    cmp_web_site = Column('cmp_web_site', String)
    is_one = Column('is_one', Integer)
    business_license_url = Column('business_license_url', String)
    organization_code = Column('organization_code', String)
    organization_code_url = Column('organization_code_url', String)
    tax_certificate = Column('tax_certificate', String)
    tax_certificate_url = Column('tax_certificate_url', String)
    legaler_identity_code = Column('legaler_identity_code', String)
    legaler_identity_url = Column('legaler_identity_url', String)
    legaler_identity_opposite_url = Column('legaler_identity_opposite_url', String)
    legaler_hold_identity_url = Column('legaler_hold_identity_url', String)
    legaler_business_url = Column('legaler_business_url', String)
    is_validated = Column('is_validated', Integer)
    service_type = Column('service_type', String)
    cmp_logo = Column('cmp_logo', String)
    cmp_scale = Column('cmp_scale', String)
    industry = Column('industry', String)
    main_business = Column('main_business', String)
    unified_code = Column('unified_code', String)
    financial_data_type = Column('financial_data_type', String)
    fill_data_talbes = Column('fill_data_talbes', String)
    db_name = Column('db_name', String)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpAptitudeInfo(Base):
    """公司资质情况信息表"""

    __tablename__ = 'wy_cmp_aptitude_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    aptitude_name = Column('aptitude_name', String)
    aptitude_code = Column('aptitude_code', String)
    certificate_authority = Column('certificate_authority', String)
    start_date = Column('start_date', DateTime)
    end_date = Column('end_date', DateTime)
    aptitude_content = Column('aptitude_content', String)
    public_date = Column('public_date', DateTime)
    status = Column('status', String)
    information = Column('information', String)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpBranchInfo(Base):
    """分支机构情况"""

    __tablename__ = 'wy_cmp_branch_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    branch_name = Column('branch_name', String)
    membership = Column('membership', Integer)
    unified_code = Column('unified_code', String)
    main_business = Column('main_business', String)
    contribution_money = Column('contribution_money', Float)
    contribution_ratio = Column('contribution_ratio', Float)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpEvolutionInfo(Base):
    """历史沿革信息"""

    __tablename__ = 'wy_cmp_evolution_log_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    evolution_date = Column('evolution_date', DateTime)
    evolution_content = Column('evolution_content', Integer)
    evolution_content_text = Column('company_nature_text', String)
    change_before = Column('change_before', String)
    change_after = Column('change_after', String)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpMainManagerInfo(Base):
    """实际控制人情况"""

    __tablename__ = 'wy_cmp_main_manager_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    financing_apply_id = Column('financing_apply_id', String)
    name = Column('name', String)
    duty_type = Column('duty_type', Integer)
    duty_type_text = Column('duty_type_text', String)
    birth = Column('birth', DateTime)
    gender = Column('gender', Integer)
    identity_code = Column('identity_code', String)
    duty = Column('duty', String)
    degree = Column('degree', Integer)
    graduated_school = Column('graduated_school', String)
    work_experience = Column('work_experience', String)
    working_years = Column('working_years', Integer)
    manager_years = Column('manager_years', Integer)
    has_illegal = Column('has_illegal', String)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpOwnershipInfo(Base):
    """公司最新的股权结构"""

    __tablename__ = 'wy_cmp_ownership_structure_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    shareholder_name = Column('shareholder_name', String)
    shareholder_type = Column('shareholder_type', Integer)
    shareholder_type_text = Column('shareholder_type_text', String)
    venture_amount = Column('venture_amount', Float)
    venture_type = Column('venture_type', Integer)
    venture_type_text = Column('venture_type_text', String)
    venture_date = Column('venture_date', DateTime)
    venture_public_date = Column('venture_public_date', DateTime)
    real_venture_amount = Column('real_venture_amount', Float)
    real_venture_type = Column('real_venture_type', Integer)
    real_venture_type_text = Column('real_venture_type_text', String)
    real_venture_date = Column('real_venture_date', DateTime)
    shareholding_ratio = Column('shareholding_ratio', Float)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpGuaranteeInfo(Base):
    """目前对外担保责任明细"""

    __tablename__ = 'wy_cmp_external_guarantee_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    secured_cmp_name = Column('secured_cmp_name', String)
    guarantee_amount = Column('guarantee_amount', Float)
    guarantee_date = Column('guarantee_date', DateTime)
    relieve_guarantee_term = Column('relieve_guarantee_term', Float)
    present_situation = Column('present_situation', Integer)
    relationship = Column('relationship', Integer)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)


class CWyCmpPenaltyInfo(Base):
    """处罚信息"""

    __tablename__ = 'wy_cmp_penalty_info'

    pk_id = Column('pk_id', Integer, primary_key=True)
    cmp_name = Column('cmp_name', String)
    business_license = Column('business_license', String)

    id = Column('id', String)
    wy_company_base_info_id = Column('wy_company_base_info_id', String)
    penalty_result = Column('penalty_result', String)
    penalty_reason = Column('penalty_reason', String)
    penalty_department = Column('penalty_department', String)
    penalty_date = Column('penalty_date', DateTime)
    create_by = Column('create_by', String)
    create_date = Column('create_date', String)
    update_by = Column('update_by', String)
    update_date = Column('update_date', String)
    remarks = Column('remarks', String)
    del_flag = Column('del_flag', Integer)
    insert_datetime = Column('insert_datetime', DateTime)